import pandas as pd
import numpy as np
import os
import datetime
#data=pd.read_excel('综合信息查询_成交回报明细_S2S4_20200207.xls')
data=pd.read_excel('综合信息查询_成交回报11038.xls')
#data=pd.read_excel('综合信息查询_成交回报明细_S5_20200218.xls')

position=list(data['成交数量'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
combine=list(data['组合编号'])
direction=list(data['委托方向'])
price=list(data['成交价格'])
amount=list(data['成交金额'])
fee1=list(data['过户费'])
fee2=list(data['经手费'])
fee3=list(data['证管费'])
fee4=list(data['印花税'])
result=[]
for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
        
    code=int(stocks[i])
    pos=position[i]
    no=int(combine[i])
    codeStr=str(code).rjust(6,'0')
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
    else:
        continue
    mydirection=1
    openamount=amount[i]
    if direction[i]=="卖出":
        mydirection=-1
        openamount=0
    fee=fee1[i]+fee2[i]+fee3[i]+fee4[i]
    codeArray=codeStr.upper().split('.')
    result.append({'code':codeStr,'codeID':codeArray[0],'market':codeArray[1],'trade':pos,'name':names[i],'direction':mydirection,'price':price[i],'amount':amount[i],'fee':fee,'openamount':openamount,'no':no})
raw=pd.DataFrame(result)
codes=list(raw['code'])




#mytrade=[]
#for code in codes:
#    data0=tradeResult[tradeResult['code']==code]
#    directionNow=0
#    position=0
#    cash=0
#    for idx, row in data0.iterrows():
#        direction=row['direction']
#        vol=row['trade']
#        price=row['price']
#        fee=row['fee']
#        if (directionNow==0) and (direction==1):
#            #开多
#            position=position+(direciton*vol)
#            cash0=vol*price*(-direction)
#            cash=cash+cash0-fee
#            pass
#        elif (directionNow==0) and (direction==-1):
#            position=position+(direciton*vol)
#            cash0=vol*price*(-direction)
#            cash=cash+cash0-fee
#            #开空
#            pass
#        elif (directionNow==1) and (direction==1):
#            position=position+(direciton*vol)
#            cash0=vol*price*(-direction)
#            cash=cash+cash0-fee
#            #加多
#            pass
#        elif (directionNow==1) and (direction==-1):
#            #平多或者反手开空
#            if (vol<position):
#                #平多
#                pass
#            elif (vol==position):
#                #平多平干净了
#                pass
#            elif (vol>position):
#                #平完多之后开空
#                pass

#            pass
#        elif (directionNow==-1) and (direction==-1):
#            position=position+(direciton*vol)
#            cash0=vol*price*(-direction)
#            cash=cash+cash0-fee
#            #加空
#            pass
#        elif (directionNow==-1) and (direction==1):
#            #平空或者反手开多
#            pass

tradeResult=raw[raw['no']==30014005]
profit=0
print("314005 start!")
mytrade=tradeResult
mytrade['position']=tradeResult['trade']*tradeResult['direction']
mytrade['cash']=tradeResult['amount']*tradeResult['direction']*(-1)
g=mytrade.groupby(['code'])['position'].sum()
df=pd.DataFrame(g)
df['cash']=mytrade.groupby(['code'])['cash'].sum()
df['position']=mytrade.groupby(['code'])['position'].sum()
df['openamount']=mytrade.groupby(['code'])['openamount'].sum()
df['fee']=mytrade.groupby(['code'])['fee'].sum()
df['price']=mytrade.groupby(['code'])['price'].mean()
df=df.dropna()
codes=list(df.index.get_level_values(0))
r=df[df['position']==0]
profit=r['cash'].sum()-r['fee'].sum()
openamount=r['openamount'].sum()

r2=df[df['position']!=0]


r2['price']=np.round(r2['price'],2)
needtobuy=r2[r2['position']<0]
needtosell=r2[r2['position']>0]
print(needtobuy)
print(needtosell)
profit0=0
if (needtobuy.shape[0]>0):
    openamount=openamount+needtobuy['openamount'].sum()
    profit0=profit0+(needtobuy['position']*needtobuy['price']+needtobuy['cash']-needtobuy['fee']).sum()
    pass
if (needtosell.shape[0]>0):
    openamount=openamount+needtosell['openamount'].sum()
    profit0=profit0+(needtosell['position']*needtosell['price']+needtosell['cash']-needtosell['fee']).sum()
    pass

profit=profit+profit0
print(f"total profit:{profit}")
print(f"unbalance profit:{profit0}")
print(f"total amount:{openamount}")


today=datetime.date.today()
formatted_today=today.strftime('%y%m%d')
def df_to_ini(folder_name,df,result,fund_id):
    if os.path.exists(folder_name):
        pass
    else:
        os.makedirs(folder_name)
    with open(folder_name + '/' + fund_id + '.ini', mode='w', encoding='gbk') as f:
        f.write('[BASKET]\n')
        f.write('Fundid1=' + fund_id + '\n')
        f.write('TAGTAG\n')
        for idx, row in df.iterrows():
            codeArray=idx.upper().split('.')
            name=result[result['code']==idx]['name'].iloc[0]
            content = codeArray[0]+'|'+codeArray[1]+'|'+name+'|'+str(int(row['to_trade_vol']))+'\n'
            f.write(content)
        f.write('ENDENDEND')
    print('%s 生成完毕' % fund_id)

mybuydf=needtobuy
mybuydf['to_trade_vol']=(np.ceil(-mybuydf['position']/100)*100)


folder_name="314005buy"+formatted_today
fund_id="t0"
df_to_ini(folder_name,mybuydf,tradeResult,fund_id)

myselldf=needtosell
myselldf['to_trade_vol']=(np.floor(myselldf['position']/100)*100)


folder_name="314005sell"+formatted_today
fund_id="t0"
df_to_ini(folder_name,myselldf,tradeResult,fund_id)


tradeResult=raw[raw['no']==314]
profit=0
print("314D start!")
mytrade=tradeResult
mytrade['position']=tradeResult['trade']*tradeResult['direction']
mytrade['cash']=tradeResult['amount']*tradeResult['direction']*(-1)
g=mytrade.groupby(['code'])['position'].sum()
df=pd.DataFrame(g)
df['cash']=mytrade.groupby(['code'])['cash'].sum()
df['position']=mytrade.groupby(['code'])['position'].sum()
df['openamount']=mytrade.groupby(['code'])['openamount'].sum()
df['fee']=mytrade.groupby(['code'])['fee'].sum()
df['price']=mytrade.groupby(['code'])['price'].mean()
df=df.dropna()
codes=list(df.index.get_level_values(0))
r=df[df['position']==0]
profit=r['cash'].sum()-r['fee'].sum()
openamount=r['openamount'].sum()

r2=df[df['position']!=0]


r2['price']=np.round(r2['price'],2)
needtobuy=r2[r2['position']<0]
needtosell=r2[r2['position']>0]
print(needtobuy)
print(needtosell)
profit0=0
if (needtobuy.shape[0]>0):
    openamount=openamount+needtobuy['openamount'].sum()
    profit0=profit0+(needtobuy['position']*needtobuy['price']+needtobuy['cash']-needtobuy['fee']).sum()
    pass
if (needtosell.shape[0]>0):
    openamount=openamount+needtosell['openamount'].sum()
    profit0=profit0+(needtosell['position']*needtosell['price']+needtosell['cash']-needtosell['fee']).sum()
    pass

profit=profit+profit0
print(f"total profit:{profit}")
print(f"unbalance profit:{profit0}")
print(f"total amount:{openamount}")


today=datetime.date.today()
formatted_today=today.strftime('%y%m%d')
def df_to_ini(folder_name,df,result,fund_id):
    if os.path.exists(folder_name):
        pass
    else:
        os.makedirs(folder_name)
    with open(folder_name + '/' + fund_id + '.ini', mode='w', encoding='gbk') as f:
        f.write('[BASKET]\n')
        f.write('Fundid1=' + fund_id + '\n')
        f.write('TAGTAG\n')
        for idx, row in df.iterrows():
            codeArray=idx.upper().split('.')
            name=result[result['code']==idx]['name'].iloc[0]
            content = codeArray[0]+'|'+codeArray[1]+'|'+name+'|'+str(int(row['to_trade_vol']))+'\n'
            f.write(content)
        f.write('ENDENDEND')
    print('%s 生成完毕' % fund_id)

mybuydf=needtobuy
mybuydf['to_trade_vol']=(np.ceil(-mybuydf['position']/100)*100)


folder_name="314Dbuy"+formatted_today
fund_id="t0"
df_to_ini(folder_name,mybuydf,tradeResult,fund_id)

myselldf=needtosell
myselldf['to_trade_vol']=(np.floor(myselldf['position']/100)*100)


folder_name="314Dsell"+formatted_today
fund_id="t0"
df_to_ini(folder_name,myselldf,tradeResult,fund_id)


tradeResult=raw[raw['no']==3140013]
#tradeResult=raw[raw['no']==15000038]
profit=0
print("3140013T02 start!")
mytrade=tradeResult
mytrade['position']=tradeResult['trade']*tradeResult['direction']
mytrade['cash']=tradeResult['amount']*tradeResult['direction']*(-1)
g=mytrade.groupby(['code'])['position'].sum()
df=pd.DataFrame(g)
df['cash']=mytrade.groupby(['code'])['cash'].sum()
df['position']=mytrade.groupby(['code'])['position'].sum()
df['openamount']=mytrade.groupby(['code'])['openamount'].sum()
df['fee']=mytrade.groupby(['code'])['fee'].sum()
df['price']=mytrade.groupby(['code'])['price'].mean()
df=df.dropna()
codes=list(df.index.get_level_values(0))
r=df[df['position']==0]
profit=r['cash'].sum()-r['fee'].sum()
openamount=r['openamount'].sum()

r2=df[df['position']!=0]


r2['price']=np.round(r2['price'],2)
needtobuy=r2[r2['position']<0]
needtosell=r2[r2['position']>0]
print(needtobuy)
print(needtosell)
profit0=0
if (needtobuy.shape[0]>0):
    openamount=openamount+needtobuy['openamount'].sum()
    profit0=profit0+(needtobuy['position']*needtobuy['price']+needtobuy['cash']-needtobuy['fee']).sum()
    pass
if (needtosell.shape[0]>0):
    openamount=openamount+needtosell['openamount'].sum()
    profit0=profit0+(needtosell['position']*needtosell['price']+needtosell['cash']-needtosell['fee']).sum()
    pass

profit=profit+profit0
print(f"total profit:{profit}")
print(f"unbalance profit:{profit0}")
print(f"total amount:{openamount}")


today=datetime.date.today()
formatted_today=today.strftime('%y%m%d')
def df_to_ini(folder_name,df,result,fund_id):
    if os.path.exists(folder_name):
        pass
    else:
        os.makedirs(folder_name)
    with open(folder_name + '/' + fund_id + '.ini', mode='w', encoding='gbk') as f:
        f.write('[BASKET]\n')
        f.write('Fundid1=' + fund_id + '\n')
        f.write('TAGTAG\n')
        for idx, row in df.iterrows():
            codeArray=idx.upper().split('.')
            name=result[result['code']==idx]['name'].iloc[0]
            content = codeArray[0]+'|'+codeArray[1]+'|'+name+'|'+str(int(row['to_trade_vol']))+'\n'
            f.write(content)
        f.write('ENDENDEND')
    print('%s 生成完毕' % fund_id)

mybuydf=needtobuy
mybuydf['to_trade_vol']=(np.ceil(-mybuydf['position']/100)*100)


folder_name="T02buy"+formatted_today
fund_id="t0"
df_to_ini(folder_name,mybuydf,tradeResult,fund_id)

myselldf=needtosell
myselldf['to_trade_vol']=(np.floor(myselldf['position']/100)*100)


folder_name="T02sell"+formatted_today
fund_id="t0"
df_to_ini(folder_name,myselldf,tradeResult,fund_id)